﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using ShopB2C.DBUtilities;
using ShopB2C.DBUtilities.SqlDbHelper;
using ShopB2C.Entity;
using ShopB2C.Interfaces;

namespace ShopB2C.SQLDataAccess {
    public class Classify : IClassify {

        #region Const Variables

        private const string _classifySelectList = "Classify_SelectList";
        private const string _classifySelect = "Classify_Select";
        private const string _classifySelectByName = "Classify_SelectByName";
        private const string _classifyAppend = "Classify_append";
        private const string _classifyUpdate = "Classify_Update";
        private const string _classifyDelete = "Classify_Delete";
        private const string _classifyBatchUpdateSequence = "Classify_BatchUpdateSequence";

        private const string _propertySelectList = "Classify_Property_SelectList";
        private const string _propertySelect = "Classify_Property_Select";
        private const string _propertyAppend = "Classify_Property_Append";
        private const string _propertyUpdate = "Classify_Property_Update";
        private const string _propertyDelete = "Classify_Property_Delete";
        private const string _propertyBatchUpdateSequence = "Classify_Property_BatchUpdateSequence";
        private const string _propertyCopy = "Classify_Property_Copy";

        private const string _propertyValSelectList = "Classify_PropertyVal_SelectList";
        private const string _propertyValSelect = "Classify_PropertyVal_Select";
        private const string _propertyValAppend = "Classify_PropertyVal_Append";
        private const string _propertyValUpdate = "Classify_PropertyVal_Update";
        private const string _propertyValDelete = "Classify_PropertyVal_Delete";
        private const string _propertyValBatchUpdateSequence = "Classify_PropertyVal_BatchUpdateSequence";

        #endregion

        public Classify(string connectionString) {
            DbBase.ConnectionString = connectionString;
        }

        #region Class

        public List<ClassifyEntity> GetClassifyByModuleKey(string moduleKey) {
            return this.GetClassify(moduleKey, -1, string.Empty, -1);
        }

        public List<ClassifyEntity> GetClassifyByModuleKeyAndId(string moduleKey, int classifyId) {
            return this.GetClassify(moduleKey, classifyId, string.Empty, -1);
        }

        public List<ClassifyEntity> GetClassifyAllChildByModuleKeyAndId(string moduleKey, int classifyId) {
            return this.GetClassify(moduleKey, -1, string.Empty, classifyId);
        }

        public List<ClassifyEntity> GetClassifyByModuleKeyAndPath(string moduleKey, string path) {
            return this.GetClassify(moduleKey, -1, path, -1);
        }

        public ClassifyEntity GetClassifyById(int classifyId) {
            ClassifyEntity ce = null;
            SqlDataReader dr = SqlDbProcedure.ExecuteReader(_classifySelect,
                DbParams<SqlParameter>.CreateParameter<int>("@ClassifyId", classifyId));
            if (dr.HasRows) {
                if (dr.Read()) {
                    ce = new ClassifyEntity() {
                        ClassifyId = (int)dr["ClassifyId"],
                        Title = dr["Title"].ToString(),
                        KeyWords = dr["KeyWords"].ToString(),
                        Description = dr["Description"].ToString(),
                        ParentClassifyId = dr["ParentClassifyId"] is DBNull ? 0 : (int)dr["ParentClassifyId"],
                        Path = dr["Path"].ToString(),
                        Childs = (int)dr["Childs"],
                        Sequence = (int)dr["Sequence"],
                        Floor = (int)dr["Floor"],
                        ModuleKey = dr["ModuleKey"].ToString()
                    };
                }
            }
            dr.Close();
            dr.Dispose();
            return ce;
        }

        public ClassifyEntity GetClassifyByName(string name, string moduleKey) {
            ClassifyEntity ce = null;
            DbParams<SqlParameter> sqlParams = new DbParams<SqlParameter>();
            sqlParams.Add<String>("@Name", 50, name).SqlDbType = SqlDbType.NVarChar;
            sqlParams.Add<String>("@ModuleKey", 50, moduleKey).SqlDbType = SqlDbType.NVarChar;
            SqlDataReader dr = SqlDbProcedure.ExecuteReader(_classifySelectByName, sqlParams.ToArray);
            if (dr.HasRows) {
                if (dr.Read()) {
                    ce = new ClassifyEntity() {
                        ClassifyId = (int)dr["ClassifyId"],
                        Title = dr["Title"].ToString(),
                        KeyWords = dr["KeyWords"].ToString(),
                        Description = dr["Description"].ToString(),
                        ParentClassifyId = dr["ParentClassifyId"] is DBNull ? 0 : (int)dr["ParentClassifyId"],
                        Path = dr["Path"].ToString(),
                        Childs = (int)dr["Childs"],
                        Sequence = (int)dr["Sequence"],
                        Floor = (int)dr["Floor"],
                        ModuleKey = dr["ModuleKey"].ToString()
                    };
                }
            }
            dr.Close();
            dr.Dispose();
            return ce;
        }

        public void ClassifyAppend(ClassifyEntity ce) {
            DbParams<SqlParameter> sqlParams = new DbParams<SqlParameter>();
            sqlParams.Add<String>("@Title", 50, ce.Title).SqlDbType = SqlDbType.NVarChar;
            sqlParams.Add<String>("@KeyWords", 500, ce.KeyWords).SqlDbType = SqlDbType.NVarChar;
            sqlParams.Add<String>("@Description", 500, ce.Description).SqlDbType = SqlDbType.NVarChar;
            sqlParams.Add<Int32>("@ParentClassifyId", ce.ParentClassifyId).SqlDbType = SqlDbType.Int;
            sqlParams.Add<Int32>("@Sequence", ce.Sequence).SqlDbType = SqlDbType.Int;
            sqlParams.Add<String>("@ModuleKey", ce.ModuleKey).SqlDbType = SqlDbType.NVarChar;
            SqlDbProcedure.ExecuteNonQuery(_classifyAppend, sqlParams.ToArray);
        }

        public void ClassifyUpdate(ClassifyEntity ce) {
            DbParams<SqlParameter> sqlParams = new DbParams<SqlParameter>();
            sqlParams.Add<Int32>("@ClassifyId", ce.ClassifyId).SqlDbType = SqlDbType.Int;
            sqlParams.Add<String>("@Title", 50, ce.Title).SqlDbType = SqlDbType.NVarChar;
            sqlParams.Add<String>("@KeyWords", 500, ce.KeyWords).SqlDbType = SqlDbType.NVarChar;
            sqlParams.Add<String>("@Description", 500, ce.Description).SqlDbType = SqlDbType.NVarChar;
            sqlParams.Add<Int32>("@ParentClassifyId", ce.ParentClassifyId).SqlDbType = SqlDbType.Int;
            sqlParams.Add<Int32>("@Sequence", ce.Sequence).SqlDbType = SqlDbType.Int;
            sqlParams.Add<String>("@ModuleKey", ce.ModuleKey).SqlDbType = SqlDbType.NVarChar;
            int result = SqlDbProcedure.ExecuteNonQuery(_classifyUpdate, sqlParams.ToArray);
            if (result == -1) {
                throw new Exception("父类不能移动到子类中");
            }
            else if (result == -2) {
                throw new Exception("不能把分类直接提升为顶级分类");
            }
        }

        public void ClassifyDelete(int classifyId) {
            int result = SqlDbProcedure.ExecuteNonQuery(_classifyDelete,
                DbParams<SqlParameter>.CreateParameter<int>("@ClassifyId", classifyId));
            if (result == -1) {
                throw new Exception("分类存在商品, 请删除或将商品移动到其它分类再重试!");
            }
        }

        public void ClassifyBatchUpdateSequence(string classifyIds, string sequences) {
            DbParams<SqlParameter> sqlParams = new DbParams<SqlParameter>();
            sqlParams.Add<String>("@ClassifyIds", 4000, classifyIds).SqlDbType = SqlDbType.NVarChar;
            sqlParams.Add<String>("@Sequences", 4000, sequences).SqlDbType = SqlDbType.NVarChar;
            SqlDbProcedure.ExecuteNonQuery(_classifyBatchUpdateSequence, sqlParams.ToArray);
        }

        #region Private Methods

        private List<ClassifyEntity> GetClassify(string moduleKey, int classifyId, string path, int topClassifyId) {
            List<ClassifyEntity> listResult = listResult = new List<ClassifyEntity>();

            DbParams<SqlParameter> sqlParams = new DbParams<SqlParameter>();
            sqlParams.Add<String>("@ModuleKey", 50, moduleKey).SqlDbType = SqlDbType.NVarChar;
            if (classifyId >= 0) {
                sqlParams.Add<Int32>("@ClassifyId", classifyId).SqlDbType = SqlDbType.Int;
            }
            if (!string.IsNullOrEmpty(path)) {
                sqlParams.Add<String>("@Path", 1000, path).SqlDbType = SqlDbType.NVarChar;
            }
            if (topClassifyId >= 0) {
                sqlParams.Add<Int32>("@TopClassifyId", topClassifyId).SqlDbType = SqlDbType.Int;
            }

            SqlDataReader dr = SqlDbProcedure.ExecuteReader(_classifySelectList, sqlParams.ToArray);


            while (dr.Read()) {
                listResult.Add(new ClassifyEntity() {
                    ClassifyId = (int)dr["ClassifyId"],
                    Title = dr["Title"].ToString(),
                    KeyWords = dr["KeyWords"].ToString(),
                    Description = dr["Description"].ToString(),
                    ParentClassifyId = dr["ParentClassifyId"] is DBNull ? 0 : (int)dr["ParentClassifyId"],
                    Path = dr["Path"].ToString(),
                    Childs = (int)dr["Childs"],
                    Sequence = (int)dr["Sequence"],
                    Floor = (int)dr["Floor"],
                    ModuleKey = dr["ModuleKey"].ToString()
                });
            }

            dr.Close();
            dr.Dispose();
            return listResult;
        }

        #endregion

        #endregion

        #region property

        public void PropertyAppend(ClassifyPropertyEntity cpe) {
            DbParams<SqlParameter> sqlParams = new DbParams<SqlParameter>();
            sqlParams.Add<String>("@Name", 50, cpe.Name).SqlDbType = SqlDbType.NVarChar;
            sqlParams.Add<Int32>("@Sequence", cpe.Sequence).SqlDbType = SqlDbType.Int;
            sqlParams.Add<Boolean>("@IsMutiple", cpe.IsMutiple).SqlDbType = SqlDbType.Bit;
            sqlParams.Add<Boolean>("@IsSearch", cpe.IsSearch).SqlDbType = SqlDbType.Bit;
            sqlParams.Add<Int32>("@ClassifyId", cpe.ClassifyId).SqlDbType = SqlDbType.Int;
            SqlDbProcedure.ExecuteNonQuery(_propertyAppend, sqlParams.ToArray);
        }

        public void PropertyUpdate(ClassifyPropertyEntity cpe) {
            DbParams<SqlParameter> sqlParams = new DbParams<SqlParameter>();
            sqlParams.Add<Int32>("@PropertyId", cpe.PropertyId).SqlDbType = SqlDbType.Int;
            sqlParams.Add<String>("@Name", 50, cpe.Name).SqlDbType = SqlDbType.NVarChar;
            sqlParams.Add<Int32>("@Sequence", cpe.Sequence).SqlDbType = SqlDbType.Int;
            sqlParams.Add<Boolean>("@IsMutiple", cpe.IsMutiple).SqlDbType = SqlDbType.Bit;
            sqlParams.Add<Boolean>("@IsSearch", cpe.IsSearch).SqlDbType = SqlDbType.Bit;
            sqlParams.Add<Int32>("@ClassifyId", cpe.ClassifyId).SqlDbType = SqlDbType.Int;
            SqlDbProcedure.ExecuteNonQuery(_propertyUpdate, sqlParams.ToArray);
        }

        public void PropertyDelete(string propertyIds) {
            SqlDbProcedure.ExecuteNonQuery(_propertyDelete,
                DbParams<SqlParameter>.CreateParameter<string>("@PropertyIds", propertyIds));
        }

        public ClassifyPropertyEntity GetPropertyById(int propertyId) {
            ClassifyPropertyEntity cpe = null;
            SqlDataReader dr = SqlDbProcedure.ExecuteReader(_propertySelect,
                DbParams<SqlParameter>.CreateParameter<int>("@PropertyId", propertyId));
            if (dr.HasRows) {
                if (dr.Read()) {
                    cpe = new ClassifyPropertyEntity() {
                        PropertyId = (int)dr["PropertyId"],
                        Name = dr["Name"].ToString(),
                        Sequence = (int)dr["Sequence"],
                        IsMutiple = (bool)dr["IsMutiple"],
                        IsSearch = (bool)dr["IsSearch"],
                        ClassifyId = (int)dr["ClassifyId"]
                    };
                }
            }
            dr.Close();
            dr.Dispose();
            return cpe;
        }

        public List<ClassifyPropertyEntity> GetPropertyByClassify(int classifyId, int pageSize, int pageIndex, out int pageCount) {
            pageCount = 0;
            List<ClassifyPropertyEntity> listResult = null;
            DbParams<SqlParameter> sqlParams = new DbParams<SqlParameter>();
            sqlParams.Add<Int32>("@PageCount", ParameterDirection.Output, 0).SqlDbType = SqlDbType.Int;
            sqlParams.Add<Int32>("@PageSize", pageSize).SqlDbType = SqlDbType.Int;
            sqlParams.Add<Int32>("@PageIndex", pageIndex).SqlDbType = SqlDbType.Int;
            sqlParams.Add<Int32>("@ClassifyId", classifyId).SqlDbType = SqlDbType.Int;
            SqlDataReader dr = SqlDbProcedure.ExecuteReader(_propertySelectList, sqlParams.ToArray);
            if (dr.HasRows) {
                listResult = new List<ClassifyPropertyEntity>();
                while (dr.Read()) {
                    pageCount = (int)dr["PageCount"];
                    listResult.Add(new ClassifyPropertyEntity() {
                        PropertyId = (int)dr["PropertyId"],
                        Name = dr["Name"].ToString(),
                        Sequence = (int)dr["Sequence"],
                        IsMutiple = (bool)dr["IsMutiple"],
                        IsSearch = (bool)dr["IsSearch"],
                        ClassifyId = (int)dr["ClassifyId"]
                    });
                }
            }
            dr.Close();
            dr.Dispose();
            return listResult;
        }

        public void PropertyBatchUpdateSequence(string propertyIds, string sequences) {
            DbParams<SqlParameter> sqlParams = new DbParams<SqlParameter>();
            sqlParams.Add<String>("@PropertyIds", 4000, propertyIds).SqlDbType = SqlDbType.NVarChar;
            sqlParams.Add<String>("@Sequences", 4000, sequences).SqlDbType = SqlDbType.NVarChar;
            SqlDbProcedure.ExecuteNonQuery(_propertyBatchUpdateSequence, sqlParams.ToArray);
        }

        public void PropertyCopy(string propertyIds, int classifyId) {
            DbParams<SqlParameter> sqlParams = new DbParams<SqlParameter>();
            sqlParams.Add<String>("@PropertyValIds", 4000, propertyIds).SqlDbType = SqlDbType.NVarChar;
            sqlParams.Add<int>("@ClassifyId", classifyId).SqlDbType = SqlDbType.Int;
            SqlDbProcedure.ExecuteNonQuery(_propertyCopy, sqlParams.ToArray);
        }

        #endregion

        #region PropertyVal

        public void PropertyValAppend(ClassifyPropertyValEntity cpve) {
            DbParams<SqlParameter> sqlParams = new DbParams<SqlParameter>();
            sqlParams.Add<String>("@Name", 50, cpve.Name).SqlDbType = SqlDbType.NVarChar;
            sqlParams.Add<Int32>("@Sequence", cpve.Sequence).SqlDbType = SqlDbType.Int;
            sqlParams.Add<Int32>("@PropertyId", cpve.PropertyId).SqlDbType = SqlDbType.Int;
            SqlDbProcedure.ExecuteNonQuery(_propertyValAppend, sqlParams.ToArray);
        }

        public void PropertyValUpdate(ClassifyPropertyValEntity cpve) {
            DbParams<SqlParameter> sqlParams = new DbParams<SqlParameter>();
            sqlParams.Add<Int32>("@PropertyValId", cpve.PropertyValId).SqlDbType = SqlDbType.Int;
            sqlParams.Add<String>("@Name", 50, cpve.Name).SqlDbType = SqlDbType.NVarChar;
            sqlParams.Add<Int32>("@Sequence", cpve.Sequence).SqlDbType = SqlDbType.Int;
            sqlParams.Add<Int32>("@PropertyId", cpve.PropertyId).SqlDbType = SqlDbType.Int;
            SqlDbProcedure.ExecuteNonQuery(_propertyValUpdate, sqlParams.ToArray);
        }

        public void PropertyValDelete(string propertyValIds) {
            SqlDbProcedure.ExecuteNonQuery(_propertyValDelete,
                DbParams<SqlParameter>.CreateParameter<string>("@PropertyValIds", propertyValIds));
        }

        public ClassifyPropertyValEntity GetPropertyValById(int propertyValId) {
            ClassifyPropertyValEntity cpve = null;
            SqlDataReader dr = SqlDbProcedure.ExecuteReader(_propertyValSelect,
                DbParams<SqlParameter>.CreateParameter<int>("@PropertyValId", propertyValId));
            if (dr.HasRows) {
                if (dr.Read()) {
                    cpve = new ClassifyPropertyValEntity() {
                        PropertyValId = (int)dr["PropertyValId"],
                        Name = dr["Name"].ToString(),
                        Sequence = (int)dr["Sequence"],
                        PropertyId = (int)dr["PropertyId"]
                    };
                }
            }
            dr.Close();
            dr.Dispose();
            return cpve;
        }

        public List<ClassifyPropertyValEntity> GetPropertyValByProperty(int propertyId,
            int pageSize, int pageIndex, out int pageCount) {
            pageCount = 0;
            List<ClassifyPropertyValEntity> listResult = null;
            DbParams<SqlParameter> sqlParams = new DbParams<SqlParameter>();
            sqlParams.Add<Int32>("@PageCount", ParameterDirection.Output, 0).SqlDbType = SqlDbType.Int;
            sqlParams.Add<Int32>("@PageSize", pageSize).SqlDbType = SqlDbType.Int;
            sqlParams.Add<Int32>("@PageIndex", pageIndex).SqlDbType = SqlDbType.Int;
            sqlParams.Add<Int32>("@PropertyId", propertyId).SqlDbType = SqlDbType.Int;
            SqlDataReader dr = SqlDbProcedure.ExecuteReader(_propertyValSelectList, sqlParams.ToArray);
            if (dr.HasRows) {
                listResult = new List<ClassifyPropertyValEntity>();
                while (dr.Read()) {
                    pageCount = (int)dr["PageCount"];
                    listResult.Add(new ClassifyPropertyValEntity() {
                        PropertyValId = (int)dr["PropertyValId"],
                        Name = dr["Name"].ToString(),
                        Sequence = (int)dr["Sequence"],
                        PropertyId = (int)dr["PropertyId"]
                    });
                }
            }
            dr.Close();
            dr.Dispose();
            return listResult;
        }

        public void PropertyValBatchUpdateSequence(string propertyValIds, string sequences) {
            DbParams<SqlParameter> sqlParams = new DbParams<SqlParameter>();
            sqlParams.Add<String>("@PropertyValIds", 4000, propertyValIds).SqlDbType = SqlDbType.NVarChar;
            sqlParams.Add<String>("@Sequences", 4000, sequences).SqlDbType = SqlDbType.NVarChar;
            SqlDbProcedure.ExecuteNonQuery(_propertyValBatchUpdateSequence, sqlParams.ToArray);
        }

        #endregion
    }
}
